﻿<cfscript>
/** 
* Spreadsheet Object service
* 
* @hint "Spreadsheet Object service" 
*/ 

component 
	displayname="public.service.util.spreadsheetObject" 
	output=false 
	{
	
	/**
	* @hint "初始化对象"
	*/
	public public.service.util.spreadsheetObject function init() output=false {
		return this;
	}
	
	/**
	* @hint "创建新的电子表格操作对象"
	* 
	* @sheetName "工作簿名"
	* @isXLSx "是否 XLSx 文件格式"
	*/
	public struct function SpreadSheetNew( required string sheetName, boolean isXLSx=false ) output=false {
		
		var obj = structNew();
		
		if (arguments.isXLSx) {
			obj['Workbook'] = createObject("java","org.apache.poi.xssf.usermodel.XSSFWorkbook").init();
		}
		else {
			obj['Workbook'] = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
		}
		
		obj['Sheet'] = obj['Workbook'].createSheet( javaCast("String", arguments.sheetName) );
		obj['RowNum'] = 0;
		
		return obj;
	}
	
	/**
	* @hint "在当前活动的电子表格添加一行数据"
	* 
	* @obj "电子表格对象"
	* @rowDataList "单行数据 使用 , 符号进行单元格分割"
	*/
	public void function SpreadsheetAddrow( required struct obj, required string rowDataList ) output=false {
		
		var rowData = createObject("java","java.lang.String").init( javaCast("String", arguments.rowDataList) ).split(",");
		var cell = "";

		obj['Row'] = obj['Sheet'].createRow(obj['RowNum']);
		
		for (var i=0; i lt arrayLen(rowData); i++) {
			cell = obj['Row'].createCell(i);
			cell.setCellValue( javaCast("String", rowData[(i+1)]) );
		}
		
		obj['RowNum'] = obj['Row'].getRowNum() + 1;
		
	}

	/**
	* @hint "添加新的工作簿"
	* 
	* @obj "电子表格对象"
	* @sheetName "工作簿名"
	*/
	public void function SpreadsheetCreateSheet( required struct obj, required string sheetName ) output=false {
		
		obj['Sheet'] = obj['Workbook'].createSheet( javaCast("String", arguments.sheetName) );
		obj['RowNum'] = 0;
		
	}

	/**
	* @hint "指定当前活动工作簿"
	* 
	* @obj "电子表格对象"
	* @sheetName "工作簿名"
	*/
	public void function SpreadsheetSetActiveSheet( required struct obj, required string sheetName ) output=false {
		
		var idx = obj['Workbook'].getSheetIndex( javaCast("String", arguments.sheetName) );
		
		obj['Workbook'].setActiveSheet(idx);
		
		obj['Sheet'] = obj['Workbook'].getSheet( javaCast("String", arguments.sheetName) );
		
		obj['RowNum'] = obj['Sheet'].getPhysicalNumberOfRows(); 
		
	}
	
	/**
	* @hint "设置单元格值"
	* 
	* @obj "电子表格对象"
	* @value "值"
	* @row "行号"
	* @column "列号"
	*/
	public void function SpreadsheetSetCellValue( required struct obj, required string value, required numeric row, required numeric column ) output=false {
		
		var sheetRow = obj['Sheet'].getRow( javaCast("int", arguments.row-1) );
		var sheetCell = sheetRow.getCell( javaCast("int", arguments.column-1) );
		
		sheetCell.setCellValue( javaCast("string", arguments.value) );
	}
	
	/**
	* @hint "设置单元格格式"
	* 
	* @obj "电子表格对象"
	* @format "格式"
	* @startRow "起始行号"
	* @startColumn "起始列号"
	* @endRow "结束行号"
	* @endColumn "结束列号"
	*/
	public void function SpreadsheetFormatCell( required struct obj, required struct format, required numeric startRow, required numeric startColumn, numeric endRow=0, numeric endColumn=0 ) output=false {
		
		var createHelper = obj['Workbook'].getCreationHelper();
		var cellStyle = obj['Workbook'].createCellStyle();
		//var cell = ;
		var sheetRow = "";
		var sheetCell = "";	
		
		var row = 0;
		var column = 0;
		
		if (arguments.endRow eq 0) { arguments.endRow = arguments.startRow; }
		if (arguments.endColumn eq 0) { arguments.endColumn = arguments.startColumn; }
		
		if (isStruct(arguments.format)) {
			
			if (structKeyExists(arguments.format, "dataformat")) {
				
				cellStyle.setDataFormat(
			        					createHelper.createDataFormat().getFormat(arguments.format.dataformat)
										);
				
			}
			
			for (row=(arguments.startRow-1); row lt arguments.endRow; row++) {

				sheetRow = obj['Sheet'].getRow( javaCast("int", row) );

				for (column=(arguments.startColumn-1); column lt arguments.endColumn; column++) {

					sheetCell = sheetRow.getCell( javaCast("int", column) );
					
					sheetCell.setCellStyle(cellStyle);
				}
			}
			
			
			
		}
		
	}
	
	/**
	* @hint "保存电子表格文件"
	* 
	* @obj "电子表格对象"
	* @filePath "物理路径"
	* @overwrite "是否覆盖"
	*/
	public void function SpreadSheetWrite( required struct obj, required string filePath, boolean overwrite=true ) output=false {
		
		var fso = createObject("java","java.io.FileOutputStream").init(javaCast("String", arguments.filePath));
		
		if (arguments.overwrite) {
			obj['Workbook'].write(fso);
			fso.close();
		}
		else {
			if (fileExists(arguments.filePath)) {
				fso.close();
				throw("IMP-S000-001: File name conflict.", "spreadsheetObject.FileExists", "targetFile '"&arguments.filePath&"' already exists.", "IMP-S000-001");
			}
		}
	}
}

</cfscript>